﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;

namespace DM
{
    [HotUpdate]
    [Description("供应商信息汇总表")]
    public class SupplierSummary : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "FSUPPLIERID";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("采购价格汇总表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = true;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "FSUPPLIERID";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, "FGYSNAME");

            DateTime FStartDate;
            DateTime FEndDate;
            string FSuppiler_idsum = "";
            StringBuilder stringBuilder = new StringBuilder();
            StringBuilder stringBuilder1 = new StringBuilder();
            StringBuilder stringBuilder2 = new StringBuilder();

            Kingdee.BOS.Orm.DataEntity.DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            StringBuilder SQLFilter = new StringBuilder();
            StringBuilder SQLFilter1 = new StringBuilder();
            FStartDate = Convert.ToDateTime(dyFilter["FStartDate"]);//开始日期
            FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//最后发货日期
            //过滤 日期
            if (!FStartDate.ToString().Contains("0001") && !FEndDate.ToString().Contains("0001"))
            {
                SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
            }
            DynamicObjectCollection dyCollection1 = dyFilter["FSUPPLIERID"] as DynamicObjectCollection;
            if (dyCollection1.Count > 0)
            {
                foreach (var dobj in dyCollection1)
                {
                    //string FSuppiler_id = Convert.ToString(dobj["FSUPPLIERID_id"]);
                    DynamicObject d = dobj["FSUPPLIERID"] as DynamicObject;
                    string FSUPPLIERIDNumber = d["Number"].ToString();
                    FSuppiler_idsum = FSuppiler_idsum + "'" + FSUPPLIERIDNumber + "'" + ",";
                }
                SQLFilter1.Append("and t.fnumber IN (" + FSuppiler_idsum.TrimEnd(',') + ")");
            }
            if (!Convert.ToString(dyFilter["FCollaborationStatus1"]).Equals(""))
            {
                SQLFilter1.Append("and t.FCollaborationStatus1='" + dyFilter["FCollaborationStatus1"] + "'");
            }
            //if (!Convert.ToString(dyFilter["FSuppiler_id"]).Equals("0"))
            //{
            //    SQLFilter1.Append("and t6.FSUPPLIERID='" + dyFilter["FSuppiler_id"] + "'");
            //}
            //if (!Convert.ToString(dyFilter["FOrgId_id"]).Equals("0"))
            //{
            //    SQLFilter1.Append("and t.FPURCHASEORGID='" + dyFilter["FOrgId_id"] + "'");
            //}
            //if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            //{
            //    SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            //}
            string FCollaborationStatus1 = Kingdee.BOS.Util.ObjectUtils.Object2String(filter.FilterParameter.CustomFilter["FCollaborationStatus1"]);
            string FStartDate1 = Kingdee.BOS.Util.ObjectUtils.Object2String(filter.FilterParameter.CustomFilter["FStartDate"]);
            string FEndDate1 = Kingdee.BOS.Util.ObjectUtils.Object2String(filter.FilterParameter.CustomFilter["FEndDate"]);
            if (FCollaborationStatus1 != null && FCollaborationStatus1.Length > 0)
            {
                stringBuilder.AppendFormat(" and t.FCollaborationStatus = '{0}'", FCollaborationStatus1);
            }
            if (FStartDate1 != null && FStartDate1.Length > 0)
            {
                stringBuilder1.AppendFormat(" and t.FDATE>='{0}'", FStartDate1);
            }
            if (FEndDate1 != null && FEndDate1.Length > 0)
            {
                stringBuilder1.AppendFormat(" and t.FDATE<='{0}'", FEndDate1);
            }
            //获取过滤方案的基础资料数据包
            var bdDataObj = filter.FilterParameter.CustomFilter["FSUPPLIERID1"] as Kingdee.BOS.Orm.DataEntity.DynamicObject;
            if (bdDataObj != null && bdDataObj.DynamicObjectType.Properties.ContainsKey("Number"))
            {
                string number = bdDataObj["Number"].ToString();
                stringBuilder2.AppendFormat(" and t.fnumber = '{0}'", number);
            }

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
---------------------------------------应付单当月采购量-----------------------------------------------------
SELECT T.FSUPPLIERID,SUM(T1.FPRICEQTY)FPRICEQTYCurrent,
SUM(T1.FALLAMOUNTFOR)FALLAMOUNTFORCurrent
INTO #A
FROM T_AP_PAYABLE T
LEFT JOIN T_AP_PAYABLEENTRY T1 ON T.FID=T1.FID
WHERE T.FDOCUMENTSTATUS='C' {2}{5}
GROUP BY T.FSUPPLIERID

---------------------------------------应付单总采购量-----------------------------------------------------
SELECT T.FSUPPLIERID,SUM(T1.FPRICEQTY)FPRICEQTYCount,
SUM(T1.FALLAMOUNTFOR)FALLAMOUNTFORCount 
INTO #B
FROM T_AP_PAYABLE T
LEFT JOIN T_AP_PAYABLEENTRY T1 ON T.FID=T1.FID
WHERE T.FDOCUMENTSTATUS='C'
GROUP BY T.FSUPPLIERID

---------------------------------------付款单、退款单当月应付金额-----------------------------------------------------
SELECT T.FCONTACTUNIT,(SUM(T1.FPAYAMOUNTFOR_E)-T2.FREFUNDAMOUNTFOR_E)FAMOUNTCurrent
INTO #C
FROM T_AP_PAYBILL T
LEFT JOIN T_AP_PAYBILLENTRY T1 ON T.FID=T1.FID
LEFT JOIN 
(
SELECT T.FCONTACTUNIT,SUM(T1.FREFUNDAMOUNTFOR_E)FREFUNDAMOUNTFOR_E FROM T_AP_REFUNDBILL T
LEFT JOIN T_AP_REFUNDBILLENTRY T1 ON T.FID=T1.FID
WHERE T.FDOCUMENTSTATUS='C' {2}{5}
GROUP BY T.FCONTACTUNIT
)T2 ON T.FCONTACTUNIT=T2.FCONTACTUNIT
WHERE T.FDOCUMENTSTATUS='C' {2}{5}
GROUP BY T.FCONTACTUNIT,T2.FREFUNDAMOUNTFOR_E

---------------------------------------付款单、退款单总应付金额-----------------------------------------------------
SELECT T.FCONTACTUNIT,(SUM(T1.FPAYAMOUNTFOR_E)-T2.FREFUNDAMOUNTFOR_E)FAMOUNTCount
INTO #D
FROM T_AP_PAYBILL T
LEFT JOIN T_AP_PAYBILLENTRY T1 ON T.FID=T1.FID
LEFT JOIN 
(
SELECT T.FCONTACTUNIT,SUM(T1.FREFUNDAMOUNTFOR_E)FREFUNDAMOUNTFOR_E FROM T_AP_REFUNDBILL T
LEFT JOIN T_AP_REFUNDBILLENTRY T1 ON T.FID=T1.FID
WHERE T.FDOCUMENTSTATUS='C'
GROUP BY T.FCONTACTUNIT
)T2 ON T.FCONTACTUNIT=T2.FCONTACTUNIT
WHERE T.FDOCUMENTSTATUS='C'
GROUP BY T.FCONTACTUNIT,T2.FREFUNDAMOUNTFOR_E


select *,{0} into {1} from (
SELECT ROW_NUMBER() OVER (ORDER BY T1.FNAME) AS FXH, T1.FNAME FGYSNAME,T.FSUPPLIERID,
CASE WHEN T.FCollaborationStatus='0' THEN '合作' WHEN T.FCollaborationStatus='1' THEN '不合作' ELSE '' END FCollaborationStatus,
T.FZQ,
T2.FPRICEQTYCurrent,T2.FALLAMOUNTFORCurrent,T3.FPRICEQTYCount,T3.FALLAMOUNTFORCount,
T4.FAMOUNTCurrent,T5.FAMOUNTCount,(ISNULL(T3.FALLAMOUNTFORCount,0)-ISNULL(T5.FAMOUNTCount,0))FPendingPayment,
T.FCollaborationStatus FCollaborationStatus1

FROM t_BD_Supplier T
LEFT JOIN T_BD_SUPPLIER_L T1 ON T.FSUPPLIERID=T1.FSUPPLIERID
LEFT JOIN #A T2 ON T.FSUPPLIERID=T2.FSUPPLIERID
LEFT JOIN #B T3 ON T.FSUPPLIERID=T3.FSUPPLIERID
LEFT JOIN #C T4 ON T.FSUPPLIERID=T4.FCONTACTUNIT
LEFT JOIN #D T5 ON T.FSUPPLIERID=T5.FCONTACTUNIT
WHERE T.FDOCUMENTSTATUS='C' {4} {6}
) t where t.FGYSNAME is not null {3} 



                                        ", seqFld, tableName, SQLFilter, SQLFilter1, stringBuilder, stringBuilder1, stringBuilder2);
            DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName)
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
        //public override ReportHeader GetReportHeaders(IRptParams filter)
        //{
        //    ReportHeader rptHeader = new ReportHeader();
        //    rptHeader.AddChild("FIDENTITYID", new Kingdee.BOS.LocaleValue("序号"));
        //    rptHeader.AddChild("FBILLNO", new Kingdee.BOS.LocaleValue("单据编号"));
        //    rptHeader.AddChild("FMATERIALNUMBER", new Kingdee.BOS.LocaleValue("物料编码"));
        //    return rptHeader;
        //}

        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            var rptTitles = new ReportTitles();
            if (filter == null || filter.FilterParameter == null || filter.FilterParameter.CustomFilter == null)
                return rptTitles;

            string FCollaborationStatus1 = Kingdee.BOS.Util.ObjectUtils.Object2String(filter.FilterParameter.CustomFilter["FCollaborationStatus1"]);
            rptTitles.Add(new ReportTitle("FCollaborationStatus1", FCollaborationStatus1));
            string FStartDate = Kingdee.BOS.Util.ObjectUtils.Object2String(filter.FilterParameter.CustomFilter["FStartDate"]);
            rptTitles.Add(new ReportTitle("FStartDate", FStartDate));
            string FEndDate = Kingdee.BOS.Util.ObjectUtils.Object2String(filter.FilterParameter.CustomFilter["FEndDate"]);
            rptTitles.Add(new ReportTitle("FEndDate", FEndDate));

            //获取过滤方案的基础资料内码
            var bdPkVal = Kingdee.BOS.Util.ObjectUtils.Object2Int(filter.FilterParameter.CustomFilter["FSUPPLIERID1_Id"]);
            if (bdPkVal != 0)
            {
                rptTitles.Add(new ReportTitle("FSUPPLIERID1", bdPkVal.ToString()));
            }

            return rptTitles;
        }
    }
}
